package com.example.oa;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * @author 23333
 */
@WebServlet("/OA")
public class EmployeeServlet extends HttpServlet {
    /**
     * 获取页面get请求处理
     * @param req
     * @param resp
     * @throws ServletException
     * @throws IOException
     */
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doPost(req,resp);
    }

    /**
     * 获取页面post请求处理
     * @param req
     * @param resp
     * @throws ServletException
     * @throws IOException
     */
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String action = req.getParameter("action");
        if(action.equals("sign_up")){
            signUp(req,resp);
        }
        if (action.equals("sign_in")) {
            signIn(req,resp);
        }
        if(action.equals("sign_out")){
            signOut(req,resp);
        }
        if (action.equals("findById")){
            findById(req,resp);
        }
        if (action.equals("update")){
            update(req,resp);
        }
        if (action.equals("deleteById")){
            deleteById(req,resp);
        }
        if (action.equals("add")){
            add(req,resp);
        }
        if (action.equals("detail")){
            detail(req,resp);
        }
    }

    private void detail(HttpServletRequest req, HttpServletResponse resp) {
        int id = Integer.parseInt(req.getParameter("id"));

        Connection connection = DBCPDatasource.getConnection();
        String sql = "select * from db_employee.employee where id = ?";
        ResultSet resultSet  = null;
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            resultSet = preparedStatement.executeQuery();
            resultSet.next();

            String name = resultSet.getString("name");
            String password = resultSet.getString("password");
            int age = resultSet.getInt("age");
            String gender = resultSet.getString("gender");
            int role = resultSet.getInt("role");

            Employee employee = new Employee(id,name,password,age,gender,role);

            req.getSession().setAttribute("employee",employee);
            resp.sendRedirect("OA/admin/detail.jsp");
        } catch (SQLException | IOException e) {
            throw new RuntimeException(e);
        }finally {
            DBCPDatasource.closeSqlConnect(connection,preparedStatement,resultSet);
        }
    }

    /**
     * 实现管理员添加用户
     * @param req
     * @param resp
     */
    private void add(HttpServletRequest req, HttpServletResponse resp){
        addEmployee(req,resp,"admin/admin.jsp","admin/add.jsp");
    }

    /**
     * 通过id删除员工
     * @param req
     * @param resp
     */
    private void deleteById(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException{
        int deleteId = Integer.parseInt(req.getParameter("id"));
        Connection connection = DBCPDatasource.getConnection();
        String sql = "delete from db_employee.employee where id = ?";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,deleteId);
            preparedStatement.executeUpdate();
            req.getSession().setAttribute("employeeList", selectEmployeeList(req,resp));
            resp.sendRedirect("OA/admin/admin.jsp");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {

            DBCPDatasource.closeSqlConnect(connection, preparedStatement,null);

        }

    }

    /**
     * 更新员工信息
     * @param req
     * @param resp
     */
    private void update(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException{
        int id = Integer.parseInt(req.getParameter("id"));
        int age = Integer.parseInt(req.getParameter("age"));
        String gender = req.getParameter("gender");
        Connection connection = DBCPDatasource.getConnection();
        String sql = "UPDATE db_employee.employee set age = ? , gender = ? where id = ?";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,age);
            preparedStatement.setString(2,gender);
            preparedStatement.setInt(3,id);
            preparedStatement.executeUpdate();
            req.getSession().setAttribute("employeeList", selectEmployeeList(req,resp));
            resp.sendRedirect("OA/admin/admin.jsp");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {

            DBCPDatasource.closeSqlConnect(connection, preparedStatement,null);
        }
    }
    /**
     * 通过id查询返回员工信息
     * @param req
     * @param resp
     */
    private void findById(HttpServletRequest req, HttpServletResponse resp) throws  IOException{
        Connection connection = DBCPDatasource.getConnection();
        int id = Integer.parseInt(req.getParameter("id"));

        String sql = "SELECT * FROM db_employee.employee WHERE id = ?";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            resultSet.next();
            String name = resultSet.getString("name");
            String gender = resultSet.getString("gender");
            int age = resultSet.getInt("age");
            Employee employee = new Employee(id,name,age,gender);
            req.getSession().setAttribute("employee",employee);
            resp.sendRedirect("OA/admin/edit.jsp");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBCPDatasource.closeSqlConnect(connection, preparedStatement,resultSet);
        }
    }

    /**
     * 账号登出注销session
     * @param req
     * @param resp
     */
    private void signOut(HttpServletRequest req, HttpServletResponse resp) throws  IOException{
        req.getSession().invalidate();
        resp.sendRedirect("OA/index.jsp");
    }
    /**
     * 查询所有非管理员员工信息
     * @param req
     * @param resp
     * @return ArrayList<Employee>
     */
    private ArrayList<Employee> selectEmployeeList(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        Connection connection = DBCPDatasource.getConnection();
        String sqlSelectEmployees = "select * from db_employee.employee where role <> 1";
        ArrayList<Employee> employeeList = new ArrayList<>();
        PreparedStatement preparedStatementSelectEmployees = null;
        ResultSet resultEmployees = null;
        try {
            preparedStatementSelectEmployees = connection.prepareStatement(sqlSelectEmployees);
            resultEmployees = preparedStatementSelectEmployees.executeQuery();
            while (resultEmployees.next()){
                Employee employee = new Employee();
                employee.setId(resultEmployees.getInt("id"));
                employee.setName(resultEmployees.getString("name"));
                employee.setAge(resultEmployees.getInt("age"));
                employee.setGender(resultEmployees.getString("gender"));
                employeeList.add(employee);
            }
            return employeeList;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBCPDatasource.closeSqlConnect(connection, preparedStatementSelectEmployees,resultEmployees);
        }
    }


    /**
     * 登陆方法
     * @param req
     * @param resp
     * @throws ServletException
     * @throws IOException
     */
    private void signIn(HttpServletRequest req, HttpServletResponse resp) throws ServletException {
        System.out.println("登陆~");
        String name = req.getParameter("name");
        String password = req.getParameter("password");

        Connection connection = DBCPDatasource.getConnection();
        String sqlSelect = "SELECT * FROM db_employee.employee WHERE name = ? and password = md5(?)";
        PreparedStatement preparedStatementSelect = null;
        ResultSet resultSet = null;
        try {
            preparedStatementSelect = connection.prepareStatement(sqlSelect);
            preparedStatementSelect.setString(1, name);
            preparedStatementSelect.setString(2, password);
            resultSet = preparedStatementSelect.executeQuery();
            System.out.println(name+password);
            if (resultSet.next()) {
                int roleId = resultSet.getInt("role");
                System.out.println(roleId);
                String nameDb = resultSet.getString("name");
                req.getSession().setAttribute("name",nameDb);
                req.getSession().setAttribute("roleId",roleId);

                if (roleId  == 1) {
                    System.out.println("管理员登陆了~");
                    req.getSession().setAttribute("employeeList", selectEmployeeList(req,resp));
                    resp.sendRedirect("OA/admin/admin.jsp");
                }else{
                    int id =resultSet.getInt("id");
                    String gender = resultSet.getString("gender");
                    int age = resultSet.getInt("age");

                    Employee employee = new Employee(id,name,age,gender);
                    req.getSession().setAttribute("employee",employee);
                    resp.sendRedirect("OA/home.jsp");
                }
            }else {
                req.setAttribute("message","用户名或密码有误，请重新输入！");
                req.getRequestDispatcher("OA/index.jsp").forward(req,resp);
            }
        }catch (SQLException | IOException e) {
            e.printStackTrace();
        }finally {
            DBCPDatasource.closeSqlConnect(connection, preparedStatementSelect,resultSet);
        }

    }
    /**
     * 注册方法
     * @param req
     * @param resp
     */
    private void signUp(HttpServletRequest req, HttpServletResponse resp) {
        addEmployee(req,resp,"index.jsp","sign_up.jsp");
    }

    /**
     * 实现添加单个员工的方法
     * @param req
     * @param resp
     * @param successPage 成功添加返回页面
     * @param errorPage 添加失败返回页面
     */
    private void addEmployee(HttpServletRequest req, HttpServletResponse resp,String successPage,String errorPage) {
        System.out.println("注册~");
        String name = req.getParameter("name");
        String password = req.getParameter("password");
        int age = Integer.parseInt(req.getParameter("age"));
        String gender = req.getParameter("gender");
        Connection connection = DBCPDatasource.getConnection();
        String sqlSelect = "SELECT * FROM db_employee.employee WHERE name = ?";
        PreparedStatement preparedStatementSelect = null;
        PreparedStatement preparedPreparedStatementInsert = null;
        ResultSet resultSet = null;
        try {
            preparedStatementSelect = connection.prepareStatement(sqlSelect);
            preparedStatementSelect.setString(1,name);
            resultSet = preparedStatementSelect.executeQuery();
            if (resultSet.next()) {
                req.setAttribute("message","用户名已存在，请重新注册！");
                req.getRequestDispatcher("OA/"+errorPage).forward(req,resp);
            }else {
                String sqlInsert = "INSERT INTO db_employee.employee(name,password,age,gender) VALUES(?,md5(?),?,?)";
                preparedPreparedStatementInsert = connection.prepareStatement(sqlInsert);
                preparedPreparedStatementInsert.setString(1, name);
                preparedPreparedStatementInsert.setString(2, password);
                preparedPreparedStatementInsert.setInt(3, age);
                preparedPreparedStatementInsert.setString(4, gender);
                preparedPreparedStatementInsert.executeUpdate();
                if(successPage.equals("admin/admin.jsp")){
                    req.getSession().setAttribute("employeeList",selectEmployeeList(req,resp));
                }
                req.setAttribute("message", "注册成功！");
                req.getRequestDispatcher("OA/"+successPage).forward(req, resp);
            }
        } catch (SQLException | ServletException | IOException e) {
            throw new RuntimeException(e);
        }finally {
            DBCPDatasource.closeSqlConnect(connection,preparedStatementSelect,null);
            DBCPDatasource.closeSqlConnect(connection,preparedPreparedStatementInsert,resultSet);
        }
    }

}
